Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports MigraDoc.DocumentObjectModel
Imports MigraDoc.Rendering

Public Class <Database>DataClass

    Public Shared Function GetConnection() As SqlConnection
        Dim connectionString As String _
            = "<ConnectionString>"
        Return New SqlConnection(connectionString)
    End Function

    Public Function getIdent_Current(ByVal Table As String) As Integer
        Dim query As String
        Dim connection As New SqlConnection
        Dim command As New SqlCommand
        Dim reader As SqlDataReader
        Dim returnValue As Integer = 0

        query = "SELECT IDENT_CURRENT('" & Table & "')"
        connection = GetConnection()
        command = New SqlCommand(query, connection)
        command.CommandType = CommandType.Text
        Try
            connection.Open()
            reader = command.ExecuteReader()
            If reader.HasRows = True Then
                While reader.Read()
                    returnValue = System.Convert.ToInt32(reader.GetValue(0))
                End While
            End If
            reader.Close()
            connection.Close()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        Finally
            connection.Close()
        End Try
        Return returnValue
    End Function

    Public Function getIdent_Incr(ByVal Table As String) As Integer
        Dim query As String
        Dim connection As New SqlConnection
        Dim command As New SqlCommand
        Dim reader As SqlDataReader
        Dim returnValue As Integer = 0

        query = "SELECT IDENT_INCR('" & Table & "')"
        connection = GetConnection()
        command = New SqlCommand(query, connection)
        command.CommandType = CommandType.Text
        Try
            connection.Open()
            reader = command.ExecuteReader()
            If reader.HasRows = True Then
                While reader.Read()
                    returnValue = System.Convert.ToInt32(reader.GetValue(0))
                End While
            End If
            reader.Close()
            connection.Close()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        Finally
            connection.Close()
        End Try
        Return returnValue
    End Function

    Public Function getAutoID(ByVal Mode As String, ByVal Table As String) As Integer
        Dim Ident_Current As Integer = getIdent_Current(Table)
        If Mode = "Last" Then
            Return Ident_Current
        ElseIf Mode = "New" Then
            Return Ident_Current + getIdent_Incr(Table)
        End If
        Return Nothing
    End Function

    Public Sub ExportToExcel(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
        Try
            If (Grid.ColumnCount > 0) Then
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
                oExcel.Visible = False
                oBook = oExcel.Workbooks.Add
                oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)

                oSheet.Range("B2").Value = Head
                oSheet.Range("B2").Font.Bold = True

                If Type = "One" Then

                    For s As Integer = 0 To Grid.ColumnCount - 1
                        oSheet.Range("B" & 4 + s).Value = Grid.Columns(s).HeaderText
                        oSheet.Range("C" & 4 + s).Value = Grid.SelectedRows.Item(0).Cells.Item(s).Value
                    Next

                    oSheet.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    Dim DataArrayHead(0, 0 To Grid.ColumnCount - 1) As Object
                    For s As Integer = 0 To Grid.ColumnCount - 1
                        DataArrayHead(0, s) = Grid.Columns(s).HeaderText
                    Next

                    oSheet.Range("B4").Resize(1, Grid.ColumnCount).Value = DataArrayHead
                    oSheet.Range("B4").Resize(1, Grid.ColumnCount).Font.Bold = True

                    Dim DataArray(0 To Grid.RowCount - 1, 0 To Grid.ColumnCount - 1) As Object
                    For r As Integer = 0 To Grid.RowCount - 1
                        For s As Integer = 0 To Grid.ColumnCount - 1
                            DataArray(r, s) = Grid.Rows.Item(r).Cells.Item(s).Value
                        Next
                    Next

                    oSheet.Range("B5").Resize(Grid.RowCount, Grid.ColumnCount).Value = DataArray
                    oSheet.Columns.AutoFit()

                End If

                oExcel.Visible = True
                oExcel = Nothing
                oSheet = Nothing
            End If
        Catch
        End Try
    End Sub

    Public Sub ExportToWord(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
        Try
            If (Grid.ColumnCount > 0) Then
                Dim oWord As Word.Application
                Dim oDoc As Word.Document
                Dim oTable As Word.Table
                Dim oPara1 As Word.Paragraph

                oWord = CType(CreateObject("Word.Application"), Word.Application)
                oWord.Visible = False
                oDoc = oWord.Documents.Add
                With oDoc.PageSetup
                    .PaperSize = Word.WdPaperSize.wdPaperA4
                    .Orientation = Word.WdOrientation.wdOrientLandscape
                    .LeftMargin = oWord.CentimetersToPoints(1)
                    .BottomMargin = oWord.CentimetersToPoints(1)
                    .TopMargin = oWord.CentimetersToPoints(1)
                End With

                oPara1 = oDoc.Content.Paragraphs.Add
                oPara1.Range.Text = Head
                oPara1.Range.Font.Bold = CInt(False)
                oPara1.Range.Font.Size = 8
                oPara1.Format.SpaceAfter = 8
                oPara1.Range.InsertParagraphAfter()

                If Type = "One" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.ColumnCount, 2)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To Grid.ColumnCount - 1
                        oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
                        oTable.Cell(i + 1, 2).Range.Text = System.Convert.ToString(IIf(Grid.SelectedRows.Item(0).Cells.Item(i).Value Is System.DBNull.Value, "", Grid.SelectedRows.Item(0).Cells.Item(i).Value))
                    Next

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To Grid.ColumnCount - 1
                        oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
                    Next

                    For r As Integer = 0 To Grid.RowCount - 1
                        For s As Integer = 0 To Grid.ColumnCount - 1
                            oTable.Cell(r + 2, s + 1).Range.Text = System.Convert.ToString(IIf(Grid.Rows.Item(r).Cells.Item(s).Value Is System.DBNull.Value, "", Grid.Rows.Item(r).Cells.Item(s).Value))
                        Next
                    Next

                    oTable.Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = Word.WdColorIndex.wdGray25

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                End If

                oWord.Visible = True
                oWord.NormalTemplate.Saved = True
                oWord = Nothing
                oDoc = Nothing
            End If
        Catch
        End Try
    End Sub

    'Public Sub ExportToPdf(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView)
    '    Try
    '        If (Grid.ColumnCount > 0) Then
    '            Dim oWord As Word.Application
    '            Dim oDoc As Word.Document
    '            Dim oTable As Word.Table
    '            Dim oPara1 As Word.Paragraph

    '            oWord = CType(CreateObject("Word.Application"), Word.Application)
    '            oWord.Visible = False
    '            oDoc = oWord.Documents.Add
    '            With oDoc.PageSetup
    '                .PaperSize = Word.WdPaperSize.wdPaperA4
    '                .Orientation = Word.WdOrientation.wdOrientLandscape
    '                .LeftMargin = oWord.CentimetersToPoints(1)
    '                .BottomMargin = oWord.CentimetersToPoints(1)
    '                .TopMargin = oWord.CentimetersToPoints(1)
    '            End With

    '            oPara1 = oDoc.Content.Paragraphs.Add
    '            oPara1.Range.Text = Head
    '            oPara1.Range.Font.Bold = CInt(False)
    '            oPara1.Range.Font.Size = 8
    '            oPara1.Format.SpaceAfter = 8
    '            oPara1.Range.InsertParagraphAfter()

    '            If Type = "One" Then

    '                oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.ColumnCount, 2)
    '                oTable.Range.ParagraphFormat.SpaceAfter = 2

    '                For i As Integer = 0 To Grid.ColumnCount - 1
    '                    oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
    '                    oTable.Cell(i + 1, 2).Range.Text = System.Convert.ToString(IIf(Grid.SelectedRows.Item(0).Cells.Item(i).Value Is System.DBNull.Value, "", Grid.SelectedRows.Item(0).Cells.Item(i).Value))
    '                Next

    '                oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
    '                oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

    '                oTable.AllowAutoFit = True
    '                oTable.Columns.AutoFit()

    '            ElseIf Type = "Many" Then

    '                oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount)
    '                oTable.Range.ParagraphFormat.SpaceAfter = 2

    '                For i As Integer = 0 To Grid.ColumnCount - 1
    '                    oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns(i).HeaderText)
    '                Next

    '                For r As Integer = 0 To Grid.RowCount - 1
    '                    For s As Integer = 0 To Grid.ColumnCount - 1
    '                        oTable.Cell(r + 2, s + 1).Range.Text = System.Convert.ToString(IIf(Grid.Rows.Item(r).Cells.Item(s).Value Is System.DBNull.Value, "", Grid.Rows.Item(r).Cells.Item(s).Value))
    '                    Next
    '                Next

    '                oTable.Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = Word.WdColorIndex.wdGray25

    '                oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
    '                oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

    '                oTable.AllowAutoFit = True
    '                oTable.Columns.AutoFit()

    '            End If

    '            Dim FilePath As String = System.IO.Path.GetTempPath & "\" & Date.Now.Second.ToString & Date.Now.Millisecond.ToString & ".pdf"
    '            oDoc.ExportAsFixedFormat(FilePath, Word.WdExportFormat.wdExportFormatPDF, True)
    '            oDoc.Close(Word.WdSaveOptions.wdDoNotSaveChanges)
    '            oWord.Quit()
    '        End If
    '    Catch
    '    End Try
    'End Sub

    Public Sub ExportToPdf(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGridView, ByVal GridRow As DataGridViewRow)
        Try
            Dim dt As DataTable
            If Type = "One" Then
                dt = DataGridViewRow_To_DataTable(Grid, GridRow, Head)
            Else
                dt = DataGridView_To_DataTable(Grid, Head)
            End If
            Dim pdfForm As New PDFform(dt, Head, Type)

            Dim document As Document = pdfForm.CreateDocument()
            document.UseCmykColor = True
            Dim pdfRenderer As New PdfDocumentRenderer(True)
            pdfRenderer.Document = document
            pdfRenderer.RenderDocument()

            Dim FilePath As String = System.IO.Path.GetTempPath & "\" & Date.Now.Second.ToString & Date.Now.Millisecond.ToString & ".pdf"
            pdfRenderer.Save(FilePath)
            Process.Start(FilePath)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.ReadLine()
        End Try
    End Sub

    'Converts the DataGridView to DataTable
    Public Function DataGridView_To_DataTable(dgv As DataGridView, tblName As [String], Optional minRow As Integer = 0) As DataTable
        Dim dt As New DataTable(tblName)

        ' Header columns
        For Each column As DataGridViewColumn In dgv.Columns
            Dim dc As New DataColumn(column.Name.ToString())
            dt.Columns.Add(dc)
        Next

        ' Data cells
        For i As Integer = 0 To dgv.Rows.Count - 1
            Dim row As DataGridViewRow = dgv.Rows(i)
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dgv.Columns.Count - 1
                dr(j) = If((row.Cells(j).Value Is Nothing), "", row.Cells(j).Value.ToString())
            Next
            dt.Rows.Add(dr)
        Next

        ' Related to the bug arround min size when using ExcelLibrary for export
        For i As Integer = dgv.Rows.Count To minRow - 1
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dt.Columns.Count - 1
                dr(j) = "  "
            Next
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

    'Converts the DataGridViewRow to DataTable
    Public Function DataGridViewRow_To_DataTable(dgv As DataGridView, dgvr As DataGridViewRow, tblName As [String], Optional minRow As Integer = 0) As DataTable
        Dim dt As New DataTable(tblName)

        ' Header columns
        Dim dc1 As New DataColumn("Column")
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Value")
        dt.Columns.Add(dc2)

        For Each column As DataGridViewColumn In dgv.Columns
            Dim dr As DataRow = dt.NewRow()
            dr.Item(0) = dgv.Columns(column.Index).Name.ToString()
            dr.Item(1) = dgvr.Cells(column.Index).Value
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

End Class

